<!DOCTYPE html>
<html>
<head>
    <title>010.事务-索引-视图</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="selected">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 010.事务-索引-视图 </h1>
            <div class="doc_post"> <h1 id="toc_0">事务 索引 视图</h1>

<p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">事务 索引 视图</a>
</li>
<li>
<a href="#toc_1">[1. 事务]</a>
</li>
<li>
<a href="#toc_4">2.隐式事务</a>
</li>
<li>
<a href="#toc_5">3.自动事务</a>
</li>
</ul>
</li>
<li>
<a href="#toc_6">事务的特性</a>
</li>
<li>
<a href="#toc_7">事务的用法:实例1-显式事务</a>
</li>
<li>
<a href="#toc_9">事务的用法:实例3 - 自动提交事务</a>
</li>
<li>
<a href="#toc_10">了解内容.错误捕捉与处理.</a>
</li>
</ul>
</li>
<li>
<a href="#toc_11">[2.索引]</a>
<ul>
<li>
<a href="#toc_12">什么是索引</a>
</li>
<li>
<a href="#toc_14">思考: 什么时候创建索引???</a>
</li>
</ul>
</li>
<li>
<a href="#toc_15">[3.视图]</a>
<ul>
<li>
<a href="#toc_16">什么是视图</a>
<ul>
<li>
<a href="#toc_17">视图练习</a>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h1 id="toc_1"><span class="md_align_center md_header" style="text-align:center; display: block;">1. 事务</span></h1>

<p class="md_block">
    <span class="md_line">为什么需要事务?<br /></span>
    <span class="md_line">比如银行转帐,一个帐户转出,一个帐户转入,需要只少两个SQL语句来执行转帐操作,保证不出错.就需要事务.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--转帐流程:1.转出 2.转入</span>
 <span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span> <span class="c1">--小明减100元</span>
 <span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span> <span class="c1">--小红加100元   </span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">见上面代码,如果上面两个语句任一个执行错了,那么转帐就有问题,钱不知去哪了.<br /></span>
    <span class="md_line">只有使用事务的功能才能保证一致.</span>
</p>


<h2 id="toc_2">事务</h2>

<p class="md_block">
    <span class="md_line">分为显式事务/隐式事务/自动提交事务</span>
</p>


<h3 id="toc_3">1.显式事务</h3>

<p class="md_block">
    <span class="md_line">基本语法:</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--事务开始(记录事务开始的位置)</span>
 <span class="k">begin</span> <span class="n">transaction</span><span class="p">;</span> <span class="c1">--简写成: begin tran;</span>
<span class="c1">--提交事务(从开始事务的地方开始提交给数据库)</span>
 <span class="k">commit</span> <span class="n">tran</span><span class="p">;</span>
<span class="c1">--回滚事务(回滚到开始事务的地方)</span>
 <span class="k">rollback</span> <span class="n">tran</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_4">2.隐式事务</h3>

<p class="md_block">
    <span class="md_line">用<strong>SET IMPLICIT_TRANSACTIONS ON;</strong>语句开启隐式事务.<br /></span>
    <span class="md_line">隐式事务一旦开启,从你写的第一个语句起它就自动从后台偷偷开始事务了,等你操作完成后,一定要记得 <strong><em>提交</em></strong> 或 <strong><em>回滚</em></strong> 操作.</span>
</p>


<h3 id="toc_5">3.自动事务</h3>

<p class="md_block md_has_block_below md_has_block_below_blockquote">
    <span class="md_line">自动事务是SQL默认的事务行为,每成执行一句语句它都自动提交到数据库中.整个事务过程默认自动的.</span>
</p>


<blockquote>

<p class="md_block">
    <span class="md_line">自动事务 就是把SET IMPLICIT_TRANSACTIONS OFF;关掉.启用显示式事务,而又没有开始显式事务(begin tran;)</span>
</p>

</blockquote>

<h2 id="toc_6">事务的特性</h2>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">事务必须具备以下四个属性，简称ACID 属性：</span>
</p>


<ol>
<li> 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行</li>
<li> 一致性（Consistency）：当事务完成时，数据必须处于一致状态</li>
<li> 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务</li>
<li> 永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性</li>
</ol>

<h2 id="toc_7">事务的用法:实例1-显式事务</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--1.显式事务(开始   结束(提交或回滚))</span>
<span class="k">BEGIN</span> <span class="n">TRAN</span><span class="p">;</span><span class="c1">--开始显式事务</span>
 <span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span> <span class="c1">--刘帅减100元</span>
 <span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span> <span class="c1">--李豪加100元    </span>
 <span class="k">COMMIT</span> <span class="n">TRAN</span><span class="p">;</span><span class="c1">--提交事务</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">实例:遇到错误回滚事务的方法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--申明一个记录错语代码累加的变量名,用来记录每个语句是否有错,最后判断回滚还是提交.</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">errors</span> <span class="nb">INT</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span>
<span class="k">BEGIN</span> <span class="n">TRANSACTION</span><span class="p">;</span> <span class="c1">--事务开始 </span>
                 
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">4</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">6</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">7</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">8</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">9</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">errors</span> <span class="o">=@</span><span class="n">errors</span> <span class="o">+</span> <span class="o">@@</span><span class="n">ERROR</span>
<span class="c1">--只要上面代码有任何错误,则@errors不等于0,则回滚事务,</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">errors</span> <span class="o">&lt;&gt;</span> <span class="mi">0</span><span class="p">)</span> 
<span class="k">BEGIN</span>
    <span class="k">ROLLBACK</span> <span class="n">TRANSACTION</span><span class="p">;</span> <span class="c1">--如果上面任一句有错,则回滚事务</span>
    <span class="n">PRINT</span> <span class="s1">&#39;执行了回滚操作&#39;</span><span class="p">;</span>
<span class="k">END</span>
<span class="k">else</span> 
<span class="k">BEGIN</span>
    <span class="k">COMMIT</span> <span class="n">TRANSACTION</span><span class="p">;</span><span class="c1">--没任何错误则提交事务</span>
    <span class="n">PRINT</span> <span class="s1">&#39;执行了提交操作&#39;</span><span class="p">;</span>
<span class="k">END</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">实例2:设置SET XACT_ABORT ON;遇到错误时自动回滚事务</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--第二种方法,打开遇错误自动回滚事务功能.下面批处理语句如没任何错误则自动提交,有错则自动回滚.</span>
<span class="k">SET</span> <span class="n">XACT_ABORT</span> <span class="k">ON</span><span class="p">;</span><span class="c1">--在批处理语句前加上这个语句</span>
<span class="k">BEGIN</span> <span class="n">TRAN</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">4</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">6</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">7</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">8</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">9</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span>
<span class="k">COMMIT</span> <span class="n">TRAN</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">注意:如果想使用显式事务,但又开启了隐式事务.记得用 SET IMPLICIT_TRANSACTIONS OFF;把隐式事务关掉.</span>
</p>


<h2 id="toc_8">事务的用法:实例2- 隐式事务.</h2>

<p class="md_block">
    <span class="md_line">用 SET IMPLICIT_TRANSACTIONS ON;开启隐式事务.</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SET</span> <span class="n">IMPLICIT_TRANSACTIONS</span> <span class="k">ON</span><span class="c1">--开启隐式事务,自动开始事务,需提交</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">4</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">-</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">100</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">6</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">200</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">7</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">300</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">8</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">400</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">9</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">st_money</span> <span class="k">SET</span> <span class="n">MONEY</span> <span class="o">=</span> <span class="n">MONEY</span><span class="o">+</span><span class="mi">500</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span>
<span class="k">COMMIT</span> <span class="n">TRAN</span><span class="p">;</span> <span class="c1">-- 提交事务在隐式事务中切记不要忘记</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_9">事务的用法:实例3 - 自动提交事务</h2>

<p class="md_block">
    <span class="md_line">自动提交事务是SQL默认的,不需要设置.每执行一个引响表的语句它会自动提交事务. 但要注意别启用了隐式事务.,如开启了用(SET IMPLICIT_TRANSACTIONS OFF)把隐式事务关掉.</span>
</p>


<h2 id="toc_10">了解内容.错误捕捉与处理.</h2>

<p class="md_block">
    <span class="md_line">最后讲个try....catch...语句,它在事务中常用到,为了解内容.<br /></span>
    <span class="md_line">try....catch...在T-SQL中进行错误捕捉与处理的语句,<br /></span>
    <span class="md_line">语法:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">begin</span> <span class="n">try</span>
<span class="c1">--正常语句,如果此语句块中内容无错,则正常执行完,而catch中的代码块不执行.</span>
<span class="c1">--但如果此语句块中内容有错,则捕捉错误后执行CATCH代码块中的内容.</span>
<span class="k">end</span> <span class="n">try</span>
<span class="k">begin</span> <span class="n">catch</span>
<span class="c1">--只有在try语句块中的内容有错时,此代码块才会被执行.</span>
<span class="k">end</span> <span class="n">catch</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">try...cathc...实例 </span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--------try catch错误捕获与处理语句(错语级别的限制)-----------------------------</span>
<span class="k">BEGIN</span> <span class="n">TRANSACTION</span><span class="p">;</span> <span class="c1">--事务开始 </span>
 <span class="k">BEGIN</span> <span class="n">TRY</span>
     <span class="k">exec</span><span class="p">(</span><span class="s1">&#39;UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1&#39;</span><span class="p">);</span> <span class="c1">--刘帅减100元</span>
     <span class="k">exec</span><span class="p">(</span><span class="s1">&#39;UPDATE st_money SET MONEYa = MONEY+100 WHERE id = 2&#39;</span><span class="p">);</span> <span class="c1">--李豪加100元(!表名有错)</span>
     <span class="k">COMMIT</span> <span class="n">TRAN</span><span class="p">;</span>                                                     <span class="c1">-- </span>
 <span class="k">END</span> <span class="n">TRY</span>
 <span class="k">BEGIN</span> <span class="n">CATCH</span>
    <span class="k">ROLLBACK</span> <span class="n">TRAN</span><span class="p">;</span>  <span class="c1">--回滚事务</span>
 <span class="k">END</span> <span class="n">CATCH</span>
</pre></div>

<!--block_code_end-->
<h1 id="toc_11"><span class="md_align_center md_header" style="text-align:center; display: block;">2.索引</span></h1>

<h2 id="toc_12">什么是索引</h2>

<blockquote>

<p class="md_block">
    <span class="md_line">SQL Server中的数据也是按页（ 4KB ）存放<br /></span>
    <span class="md_line">索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。<br /></span>
    <span class="md_line">索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。<br /></span>
    <span class="md_line">索引的作用：通过使用索引，可以大大提高数据库的检索(查询)速度，改善数据库性能。</span>
</p>


<h2 id="toc_13">索引的类型</h2>

<p class="md_block">
    <span class="md_line">主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空<br /></span>
    <span class="md_line">唯一索引：唯一索引不允许两行具有相同的索引值<br /></span>
    <span class="md_line">聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个<br /></span>
    <span class="md_line">非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个</span>
</p>

</blockquote>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line md_line_dom_embed"><code>注:</code></span>
</p>


<ol>
<li> 因为在创建主键的时候,也创建了主索引,也是聚集索引,而且一个表只能有一个聚集索引,那在创建主键的时候就建好了.</li>
<li> 唯一索引,是为了表示列的唯一性,我们一般创建唯一约束,(基本上也用不上)</li>
<li>非聚集索引是我们的创建索引的重点部分.但由用create index默认就是非 聚集索引,</li>
</ol>

<p class="md_block">
    <span class="md_line">所以...我们学习语句只要学习简单创建索引就行了.<br /></span>
    <span class="md_line">CREATE INDEX 索引名 ON 表名(列名) WITH FILLFACTOR=X<br /></span>
    <span class="md_line">填充因子的值 <code>X</code> 取值0-100  但0与100无实际意义(都是100%填充).</span>
</p>


<h2 id="toc_14">思考: 什么时候创建索引???</h2>

<h1 id="toc_15"><span class="md_align_center md_header" style="text-align:center; display: block;">3.视图</span></h1>

<h2 id="toc_16">什么是视图</h2>

<ul>
<li>  视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上</li>
<li>视图中并不存放数据，而是存放在视图所引用的原始表（基表）中</li>
<li>同一张原始表，根据不同用户的不同需求，可以创建不同的视图</li>
</ul>

<p class="md_block">
    <span class="md_line">总结:视图就是一个查询结果的SQL定义起的别名.<br /></span>
    <span class="md_line">基本语法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--创建视图</span>
<span class="k">create</span> <span class="k">view</span> <span class="err">视图名</span> <span class="k">as</span> <span class="err">查询语句</span>
<span class="c1">--删除视图</span>
<span class="k">drop</span> <span class="k">view</span> <span class="err">视图名</span>
<span class="c1">--修改视图(把创建时的语句中的create 改成 alter)</span>
<span class="k">alter</span> <span class="k">view</span> <span class="err">视图名</span> <span class="k">as</span> <span class="err">新的查询语句</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_17">视图练习</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--1.创建一个视图名为:五班JAVA基础成绩,</span>
<span class="c1">--要求显示(学号,姓名,性别,JAVA基础成绩),并按分数从高到低排列</span>

<span class="k">create</span> <span class="k">view</span> <span class="err">五班</span><span class="n">JAVA</span><span class="err">基础成绩</span> <span class="k">as</span>
<span class="k">select</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">name</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">sex</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="n">score</span> <span class="k">from</span> <span class="n">st_info</span> <span class="n">a</span> 
<span class="k">join</span> <span class="n">st_score</span> <span class="n">b</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">uid</span> 
<span class="k">where</span> 
<span class="n">b</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span> <span class="k">AND</span> <span class="n">a</span><span class="p">.</span><span class="n">classid</span> <span class="o">=</span> <span class="mi">5</span>


<span class="c1">--2.创建一个视图:五班学生成绩视图,</span>
<span class="c1">--要求显示(学号,姓名,小组名,科目名,成绩)五个列.</span>
<span class="k">create</span> <span class="k">view</span> <span class="err">五班学生成绩视图</span> <span class="k">AS</span>
<span class="k">SELECT</span> <span class="n">A</span><span class="p">.</span><span class="n">id</span> <span class="err">学号</span><span class="p">,</span><span class="n">A</span><span class="p">.</span><span class="n">name</span> <span class="err">姓名</span><span class="p">,</span><span class="n">B</span><span class="p">.</span><span class="n">name</span> <span class="err">小组</span><span class="p">,</span><span class="n">D</span><span class="p">.</span><span class="n">name</span> <span class="err">科目</span><span class="p">,</span><span class="k">C</span><span class="p">.</span><span class="n">score</span> <span class="err">成绩</span> <span class="k">FROM</span> <span class="n">st_info</span> <span class="n">A</span>
<span class="k">JOIN</span> <span class="n">st_group</span> <span class="n">B</span> <span class="k">ON</span> <span class="n">A</span><span class="p">.</span><span class="n">groupid</span> <span class="o">=</span> <span class="n">B</span><span class="p">.</span><span class="n">id</span>
<span class="k">JOIN</span> <span class="n">st_score</span> <span class="k">C</span> <span class="k">ON</span> <span class="n">A</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="k">C</span><span class="p">.</span><span class="n">uid</span>
<span class="k">JOIN</span> <span class="n">subject</span> <span class="n">D</span> <span class="k">ON</span> <span class="k">C</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="n">D</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">A</span><span class="p">.</span><span class="n">classid</span> <span class="o">=</span> <span class="mi">5</span> 

<span class="c1">--3.把学生库七个表写成中文视图名+中文列名的七个视图.</span>
<span class="k">create</span> <span class="k">view</span> <span class="err">学生信息表视图</span> <span class="k">AS</span>
<span class="k">SELECT</span> <span class="n">id</span> <span class="err">学号</span><span class="p">,</span><span class="n">name</span> <span class="err">姓名</span><span class="p">,</span><span class="n">sex</span> <span class="err">性别</span><span class="p">,</span><span class="n">qq</span> <span class="n">QQ</span><span class="err">号</span><span class="p">,</span><span class="n">tel</span> <span class="err">电话</span><span class="p">,</span><span class="n">classid</span> <span class="err">班级</span><span class="p">,</span><span class="n">groupid</span> <span class="err">小组</span><span class="p">,</span><span class="n">workid</span> <span class="err">职责</span><span class="p">,</span><span class="n">kaid</span> <span class="err">身份证号码</span> <span class="k">FROM</span> <span class="n">st_info</span>
 
 

<span class="c1">--视图创建好了,怎么使用....(简化复杂的查询)</span>
<span class="k">select</span> <span class="err">姓名</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="err">成绩</span><span class="p">)</span> <span class="k">from</span> <span class="err">五班学生成绩视图</span> <span class="k">where</span> <span class="err">成绩</span> <span class="o">&gt;</span> <span class="mi">0</span> <span class="k">group</span> <span class="k">by</span> <span class="err">姓名</span> <span class="k">order</span> <span class="k">by</span> <span class="k">AVG</span><span class="p">(</span><span class="err">成绩</span><span class="p">)</span> 

<span class="c1">--删除视图</span>
 <span class="k">drop</span> <span class="k">view</span> <span class="err">五班学生成绩视图</span>
 
 <span class="c1">--修改视图</span>
<span class="k">alter</span> <span class="k">view</span> <span class="err">五班学生成绩视图</span> <span class="k">as</span>
<span class="k">SELECT</span> <span class="n">A</span><span class="p">.</span><span class="n">id</span> <span class="err">学号</span><span class="p">,</span><span class="n">A</span><span class="p">.</span><span class="n">name</span> <span class="err">姓名</span><span class="p">,</span><span class="n">B</span><span class="p">.</span><span class="n">name</span> <span class="err">小组</span><span class="p">,</span><span class="n">D</span><span class="p">.</span><span class="n">name</span> <span class="err">科目</span><span class="p">,</span><span class="k">C</span><span class="p">.</span><span class="n">score</span> <span class="err">成绩</span> <span class="k">FROM</span> <span class="n">st_info</span> <span class="n">A</span>
<span class="k">JOIN</span> <span class="n">st_group</span> <span class="n">B</span> <span class="k">ON</span> <span class="n">A</span><span class="p">.</span><span class="n">groupid</span> <span class="o">=</span> <span class="n">B</span><span class="p">.</span><span class="n">id</span>
<span class="k">JOIN</span> <span class="n">st_score</span> <span class="k">C</span> <span class="k">ON</span> <span class="n">A</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="k">C</span><span class="p">.</span><span class="n">uid</span>
<span class="k">JOIN</span> <span class="n">subject</span> <span class="n">D</span> <span class="k">ON</span> <span class="k">C</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="n">D</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">A</span><span class="p">.</span><span class="n">classid</span> <span class="o">=</span> <span class="mi">5</span> 
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>